from django.http import HttpResponse, JsonResponse
from django.views.decorators.http import require_http_methods
import base64, json
from .confs.conf import get_mirror_conn
from openpyxl import Workbook
from openpyxl.styles import numbers
from io import BytesIO
from datetime import datetime,timedelta

def_response_o = {
    "code": 0,
    "result": "",
    "message": "",
    "type": 'success',
  }

select_order_thanks="""
select tm,payment,count(tl) AS 'num',evname from 
(select uid,SUBSTRING(DATE_ADD(`time`, INTERVAL 8 HOUR), 1, 10) AS tm,SUBSTRING_INDEX(SUBSTRING_INDEX(content,",pay_method:",-1),',',1) as payment, COUNT(*) AS tl,evname from gather_record  
WHERE evname in ('ev_name') AND `time`>= 'star_time' AND `time`< 'end_time' AND `host` = 'site_str'
    AND sip not in (select ip from `dlz_exclude_ip`)  and SUBSTRING_INDEX(SUBSTRING_INDEX(content,",uemail:",1),'total:',-1)!=0
GROUP BY tm,payment, uid) as tb
GROUP BY tm,payment order by tm desc,payment
"""

select_checkout_create="""
select tm,payment,count(tl) AS 'num',evname from 
(select uid,SUBSTRING(DATE_ADD(`time`, INTERVAL 8 HOUR), 1, 10) AS tm,SUBSTRING_INDEX(SUBSTRING_INDEX(content,",pay_method:",-1),',','1') as payment, COUNT(*) AS tl,evname from gather_record  
WHERE evname in ('ev_name') AND `time`>= 'star_time' AND `time`< 'end_time' AND `host` = 'site_str'
 AND sip not in (select ip from `dlz_exclude_ip`)
 and SUBSTRING_INDEX(SUBSTRING_INDEX(content,"order_id:",1),'total:',-1) !=""
 and SUBSTRING_INDEX(SUBSTRING_INDEX(content,",order_id:",1),'total:',-1)!=0
GROUP BY tm,payment, uid) as tb
GROUP BY tm,payment order by tm desc,payment;
"""

select_checkout = '''
select tm,payment,count(tl) AS 'num',evname from 
(select uid,SUBSTRING(DATE_ADD(`time`, INTERVAL 8 HOUR), 1, 10) AS tm,SUBSTRING_INDEX(content,"pay_method:",-1) as payment, COUNT(*) AS tl,evname from gather_record  
WHERE evname in ('ev_name') AND `time`>= 'star_time' AND `time`< 'end_time' AND `host` = 'site_str'
AND sip not in (select ip from `dlz_exclude_ip`) AND url like 'like_str'
and SUBSTRING_INDEX(content,"pay_method:",-1) != "" and SUBSTRING_INDEX(content,"pay_method:",-1)!='v'
GROUP BY tm,payment, uid) as tb
GROUP BY tm,payment order by tm desc,payment;
'''

def get_data(start:datetime,end:datetime,num_days:int,site:str):
    #处理时间
    next_star = start - timedelta(hours=8)
    next_end = end + timedelta(days=1) - timedelta(hours=8)
    result=[]
    #查询checkout页面展示次数get_data
    tp1 = "load-page"
    db_conn_1 = get_mirror_conn()
    sql_1 = f'''
        {select_checkout.replace('star_time', str(next_star))
        .replace('end_time', str(next_end)).replace('ev_name', tp1)
        .replace('site_str', site)
        .replace("like_str", str('%%checkout%%'))};
            '''
    print(sql_1)
    r1 = db_conn_1.query_all(sql_1)
    print(r1)

    #查询checkout_create时间次数
    tp2 = "checkout-create"
    db_conn_2 = get_mirror_conn()
    sql_2 = f'''
        {select_checkout_create.replace('star_time', str(next_star))
        .replace('end_time', str(next_end)).replace('ev_name', tp2)
        .replace('site_str', site)};
            '''
    print(sql_2)
    r2 = db_conn_2.query_all(sql_2)

    #查询order-thanks次数
    tp3 = "order-thanks"
    db_conn_3 = get_mirror_conn()
    sql_3 = f'''
        {select_checkout_create.replace('star_time', str(next_star))
        .replace('end_time', str(next_end)).replace('ev_name', tp3)
        .replace('site_str', site)};
            '''
    print(sql_3)
    r3 = db_conn_3.query_all(sql_3)

    #处理数据
    for one_checktout in r1:
        one_result = {}
        #日期
        date_day=one_checktout['tm']
        #checkout页面展示次数 该支付渠道在checkout页面中展示的次数
        checkout_num=0
        #订单数 该支付渠道生成的所有订单数
        checkout_create_num=0
        #成功订单数 支付成功的订单数量
        order_thanks_num=0
        # 支付渠道名称
        payment = one_checktout['payment']

        #判断是否存在苹果支付 长度大于1为存在 剩下的单一项目
        payment_list=payment.split(",")
        if len(payment_list)>1:
            print(payment_list)
            payment_str=payment_list[1]
            payment=payment_str
            #匹配多次的苹果支付
            for one_checkout_copy in r1:
                if one_checkout_copy['tm'] == one_checktout['tm'] and payment_str in one_checkout_copy["payment"]:
                    checkout_num = checkout_num + one_checkout_copy['num']

            # 匹配checkout_create数据
            for one_checktout_create in r2:
                if one_checktout_create['tm'] == one_checktout['tm'] and one_checktout_create['payment'] == payment_str:
                    checkout_create_num = one_checktout_create['num']

            # 匹配order-thanks数据
            for one_order_thanks in r3:
                if one_order_thanks['tm'] == one_checktout['tm'] and one_order_thanks['payment'] == payment_str:
                    order_thanks_num = one_order_thanks['num']
        else:
            # 匹配多次的苹果支付
            for one_checkout_copy in r1:
                if one_checkout_copy['tm'] == one_checktout['tm'] and one_checktout['payment'] in one_checkout_copy["payment"]:
                    checkout_num = checkout_num + one_checkout_copy['num']

            # 匹配checkout_create数据
            for one_checktout_create in r2:
                if one_checktout_create['tm'] == one_checktout['tm'] and one_checktout_create['payment'] == one_checktout['payment']:
                    checkout_create_num = one_checktout_create['num']

            # 匹配order-thanks数据
            for one_order_thanks in r3:
                if one_order_thanks['tm'] == one_checktout['tm'] and one_order_thanks['payment'] == one_checktout['payment']:
                    order_thanks_num = one_order_thanks['num']

        #C2O 订单数 / checkout页面展示次数
        c2_o = round((checkout_create_num / checkout_num), 4) if checkout_num else 000
        #O2P 成功订单数 / 订单数
        o2_p = round((order_thanks_num / checkout_create_num), 4) if checkout_create_num else 000
        #C2P 成功订单数 / checkout页面展示次数
        c2_p = round((order_thanks_num / checkout_num), 4) if checkout_num else 000

        #返回数据
        one_result['date_day'] = date_day
        one_result['payment'] = payment
        one_result['checkout_num'] = checkout_num
        one_result['checkout_create_num'] = checkout_create_num
        one_result['order_thanks_num'] = order_thanks_num
        one_result['c2_o'] = to_format_str(c2_o)
        one_result['o2_p'] = to_format_str(o2_p)
        one_result['c2_p'] = to_format_str(c2_p)
        result.append(one_result)
        print(result)
        
        #去重
        unique_list = []
        if result and unique_list:
            [unique_list.append(x) for x in result if x not in unique_list]
    return unique_list

def to_format_str(params_int):
    return format(params_int, '.2%')

@require_http_methods(["GET"])
def payment_statistics_days(request):
    global select
    params = request.GET
    re = json.loads(json.dumps(def_response_o,ensure_ascii=False))
    start = params['start'] if 'start' in params.keys() else ''
    end = params['end'] if 'end' in params.keys() else ''
    site = params['site'] if 'site' in params.keys() else ''
    if not start or not end or not site:
        re['code'] = 404
        re['type'] = 'fail'
        re['message'] = "请指定起止日期和站点"
        return JsonResponse(re)
    start_day=start.split(' ')[0]
    end_day=end.split(' ')[0]
    start = datetime.strptime(start_day, "%Y-%m-%d")
    end = datetime.strptime(end_day, "%Y-%m-%d")
    delta = end - start
    num_days = delta.days
    if num_days>15:
        re['code'] = 404
        re['type'] = 'fail'
        re['message'] = "时间间隔不能大于15天"
        return JsonResponse(re)
    result=get_data(start,end,num_days+1,site)
    print(result)
    re['result'] = result
    return JsonResponse(re)

@require_http_methods(["GET"])
def payment_statistics_excel_days(request):
    global select
    params = request.GET
    re = json.loads(json.dumps(def_response_o,ensure_ascii=False))
    start = params['start'] if 'start' in params.keys() else ''
    end = params['end'] if 'end' in params.keys() else ''
    site = params['site'] if 'site' in params.keys() else ''
    if not start or not end or not site:
        re['code'] = 404
        re['type'] = 'fail'
        re['message'] = "请指定起止日期和站点"
        return JsonResponse(re)
    start_day=start.split(' ')[0]
    end_day=end.split(' ')[0]
    start = datetime.strptime(start_day, "%Y-%m-%d")
    end = datetime.strptime(end_day, "%Y-%m-%d")
    delta = end - start
    num_days = delta.days
    if num_days>15:
        re['code'] = 404
        re['type'] = 'fail'
        re['message'] = "时间间隔不能大于15天"
        return JsonResponse(re)
    result=get_data(start,end,num_days+1,site)

    start=str(start).split(" ")[0]
    end=str(end).split(" ")[0]
    title_name = f'{start}-to-{end}'
        # 创建一个Excel工作簿
    excel_file = Workbook()
    sheet = excel_file.active
    sheet.title = title_name

    sheet[f'A1'] = "日期"
    sheet[f'B1'] = "支付渠道名称"
    sheet[f'C1'] = "checkout页面展示次数"
    sheet[f'D1'] = "订单数"
    sheet[f'E1'] = "成功订单数"
    sheet[f'F1'] = "C2O"
    sheet[f'G1'] = "O2P"
    sheet[f'H1'] = "C2P"

    for i in range(len(result)):
        no = i+1

        # 添加一些数据
        sheet[f'A{no+1}'] = result[i]['date_day']
        sheet[f'B{no+1}'] = result[i]['payment']
        sheet[f'C{no+1}'] = result[i]['checkout_num']
        sheet[f'D{no+1}'] = result[i]['checkout_create_num']
        sheet[f'E{no+1}'] = result[i]['order_thanks_num']
        sheet[f'F{no+1}'] = result[i]['c2_o']
        sheet[f'G{no+1}'] = result[i]['o2_p']
        sheet[f'H{no+1}'] = result[i]['c2_p']
    
    # 保存工作簿到内存中的BytesIO
    excel_bytes_io = BytesIO()
    excel_file.save(excel_bytes_io)
    excel_bytes_io.seek(0)
    
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = f'attachment; filename={title_name}'
    response.write(excel_bytes_io.getvalue())
    return response